package com.crm.dao;

import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.cfg.AnnotationConfiguration;
import com.crm.entity.Parts;

import com.crm.entity.Clientdet;
import com.crm.entity.Business;
import com.crm.entity.Opportunity;
import com.crm.entity.Executives;
import com.crm.util.ApplicationContext;

public class BusinessDAO {
	protected final Log logger = LogFactory.getLog(getClass());
	
	public  int insert(Business c) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
		     tx = session.beginTransaction();
			session.saveOrUpdate(c);
			tx.commit();
			return 0;
		}
		catch (Exception ex) {
		     if (tx!=null) tx.rollback();
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	public  Business get(String name) throws HibernateException, Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String querystr="From Business where shortname=:name";
			Query query=session.createQuery(querystr);
			query.setString("name", name);
			Object queryresult=query.uniqueResult();
			Business comp=(Business)queryresult;
			return comp;
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	public  Business getBusiness(String name) throws HibernateException, Exception {
			Session session =  ApplicationContext.getHiberSession().openSession();
			try {
				String querystr="From Business where shortname=:name";
				Query query=session.createQuery(querystr);
				query.setString("name", name);
				Object queryresult=query.uniqueResult();
				Business comp=(Business)queryresult;
				return comp;
			}
			catch (Exception ex) {
			     throw ex;
			}
			finally {
			     session.close();
			}
				
		}
	public  List<Business> getAllCompanies() throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			List<Business> custObjs=new ArrayList<Business>();
			List objs=null;
			String querystr="From Business where is_deleted!=1";
			Query query=session.createQuery(querystr);
			objs=query.list();
			for(Object o:objs){
				custObjs.add((Business)o);
			}
			return custObjs;
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	public  Business findById(int i) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String qrystr="From Business where id=:pid";
			Query qry=session.createQuery(qrystr);
			qry.setInteger("pid", i);
			Object ob=qry.uniqueResult();
			Business p=(Business)ob;
			return p;
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}

	}
	public  void deleteCom(int compid)  throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
		     tx = session.beginTransaction();
			String qtr="Update Business set is_deleted=1 where id=:id";
			Query qry=session.createQuery(qtr);
			qry.setInteger("id", compid);	
			qry.executeUpdate();
			tx.commit();
			return ;
		}
		catch (Exception ex) {
		     if (tx!=null) tx.rollback();
		     throw ex;
		}
		finally {
		     session.close();
		}
		
	}
	public  Business getShrt(int busid,String compName) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String qtr="From Business where business_id=:busid";
			Query qry=session.createQuery(qtr);
			qry.setInteger("busid", busid);
			Object ob=qry.uniqueResult();		
			Business c=(Business)ob;
			return c;
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	public List<Business> getBusiness(int busid) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String qtr="From Business where business_id=:busid";
			Query qry=session.createQuery(qtr);
			qry.setInteger("busid", busid);	
			List<Business> qr=qry.list();	
			return qr;
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	public Business getCompId(int busId, String shrtName) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String querystr="From Business where business_id=:busId and shortname=:name";
			Query query=session.createQuery(querystr);
			query.setString("name", shrtName);
			query.setInteger("busId", busId);
			Object queryresult=query.uniqueResult();
			Business comp=(Business)queryresult;
			return comp;
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	public Business getCompById(int id) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String querystr="From Business where id=:id";
			Query query=session.createQuery(querystr);
			query.setInteger("id",id);
			Object queryresult=query.uniqueResult();
			Business comp=(Business)queryresult;
			return comp;
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
		
	}
	
	public  Business getBusinessDet(String userName) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			Query query=session.createQuery("from Business WHERE UNAME=:userName");
			query.setString("userName", userName);
			Object ob=query.uniqueResult();
			Business u=(Business)ob;
			return u;
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}

	public  Business validatCredentials(String user, String pass) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
				Query query = session.createQuery("from Business where email_id=:email and pwd=:pass");
				query.setString("email", user);
				query.setString("pass", pass);
				Business u = (Business) query.uniqueResult();
				return u;
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}

	public  Business getCompany(Integer id) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			Query query = session.createQuery("from Business where id=:compid");
			query.setInteger("compid", id);
			Object o = query.uniqueResult();
			Business c = (Business) o;
			return c;
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}

	public Business getBusinessLoginDetails(String email) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			Query query = session.createQuery("from Business where email_id=:email");
			query.setString("email", email);
			Business u = (Business) query.uniqueResult();
			return u;
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	public Business getBusinessById(int id) throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			Query query = session.createQuery("from Business where id=:id");
			query.setInteger("id",id);
			Business u = (Business) query.uniqueResult();
			return u;
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	public void updateLogo(int busid, byte[] logo) throws Exception{
		Session session =  ApplicationContext.getHiberSession().openSession();
		Transaction tx=null;
		try {
		     tx = session.beginTransaction();
			Query query = session.createQuery("update Business set logo=:logo whereid=:busid");
			query.setInteger("busid",busid);
			query.setBinary("logo", logo);
			query.executeUpdate();
			tx.commit();
			return ;
		}
		catch (Exception ex) {
		     if (tx!=null) tx.rollback();
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	
	public  Clientdet getCompShrtName(String enqnum)throws Exception {
		Session session =  ApplicationContext.getHiberSession().openSession();
		try {
			String querystr="From Opportunity opp where opp.id=:enq";
			Query query=session.createQuery(querystr);
			query.setString("enq", enqnum);
			Object queryresult=query.uniqueResult();
			
			Opportunity opp=(Opportunity)queryresult;
			String orgname=opp.getOrganisation();
			
			String qurystr="From Clientdet cli where cli.organisation=:accname";
			Query qry=session.createQuery(qurystr);
			qry.setString("accname", orgname);
			Object qryresult=qry.uniqueResult();
			Clientdet cli=(Clientdet)qryresult;
			return cli;
		}
		catch (Exception ex) {
		     throw ex;
		}
		finally {
		     session.close();
		}
	}
	
}
